# Copyright (c) 2024, Frappe Technologies Pvt. Ltd. and Contributors
# License: GNU General Public License v3. See license.txt

from __future__ import unicode_literals
import frappe
from frappe import _
from frappe.utils import getdate, add_days, get_first_day, get_quarter_start, get_year_start
import json  # 引入json模块用于序列化


def execute(filters=None):
    filters = frappe._dict(filters or {})
    validate_filters(filters)
    
    # 加载账户-报表项映射关系（按公司+账户组合）
    account_mappings = get_account_mappings(filters)
    
    columns = get_columns(filters)
    data = get_data(filters, account_mappings)
    
    return columns, data, None, None, None, get_report_summary(data)


def validate_filters(filters):
    # 确保report_date存在
    if not filters.report_date:
        filters.report_date = getdate()
    else:
        filters.report_date = getdate(filters.report_date)
    
    # 处理公司筛选器（确保是列表类型）
    if "company" not in filters or not filters.company:
        filters.company = []
    elif not isinstance(filters.company, list):
        filters.company = [filters.company]


def get_account_mappings(filters):
    """获取所有公司的account与report_item映射，返回{(company, account): report_item}"""
    mappings = {}
    
    # 获取需要查询的公司列表（筛选器选中的公司或所有公司）
    companies = filters.company if filters.company else frappe.get_all("Company", pluck="name")
    
    for company in companies:
        if frappe.db.exists("Payment Ledger Mapping", {"company": company}):
            doc = frappe.get_doc("Payment Ledger Mapping", {"company": company})
            for item in doc.account_mappings:
                mappings[(company, item.account)] = item.report_item
    
    return mappings


def get_columns(filters):
    columns = []
    
    # 始终显示Report Item列
    columns.append({
        "label": _("Report Item"),
        "fieldname": "report_item",
        "fieldtype": "Data",
        "width": 180
    })
    
    # 条件显示Original Account列
    if not filters.get("remove_original_account_column"):
        columns.append({
            "label": _("Original Account"),
            "fieldname": "account",
            "fieldtype": "Link",
            "options": "Account",
            "width": 180
        })
    
    # 条件显示Voucher Type列
    if not filters.get("remove_voucher_type_column"):
        columns.append({
            "label": _("Voucher Type"),
            "fieldname": "voucher_type",
            "fieldtype": "Data",
            "width": 120
        })
    
    # 条件显示Account Type列
    if not filters.get("remove_account_type_column"):
        columns.append({
            "label": _("Account Type"),
            "fieldname": "account_type",
            "fieldtype": "Data",
            "width": 120
        })
    
    # 条件显示Company列
    if not filters.get("remove_company_column"):
        columns.append({
            "label": _("Company"),
            "fieldname": "company",
            "fieldtype": "Link",
            "options": "Company",
            "width": 120
        })
    
    # 金额列（固定显示）
    amount_columns = [
        {
            "label": _("Today"),
            "fieldname": "today",
            "fieldtype": "Currency",
            "options": "currency",
            "width": 150
        },
        {
            "label": _("This Week"),
            "fieldname": "this_week",
            "fieldtype": "Currency",
            "options": "currency",
            "width": 150
        },
        {
            "label": _("This Month"),
            "fieldname": "this_month",
            "fieldtype": "Currency",
            "options": "currency",
            "width": 150
        },
        {
            "label": _("This Quarter"),
            "fieldname": "this_quarter",
            "fieldtype": "Currency",
            "options": "currency",
            "width": 150
        },
        {
            "label": _("This Year"),
            "fieldname": "this_year",
            "fieldtype": "Currency",
            "options": "currency",
            "width": 150
        }
    ]
    columns.extend(amount_columns)
    
    return columns


def get_data(filters, account_mappings):
    # 日期范围计算
    report_date = filters.report_date
    week_start = add_days(report_date, -report_date.weekday())
    month_start = get_first_day(report_date)
    quarter_start = get_quarter_start(report_date)
    year_start = get_year_start(report_date)

    # 确定需要显示的列，用于构建GROUP BY
    group_by_fields = []
    select_fields = []
    
    # Report Item是计算字段，不参与GROUP BY
    select_fields.append("'' AS report_item")
    
    # 原始账户（条件包含）
    if not filters.get("remove_original_account_column"):
        group_by_fields.append("account")
        select_fields.append("account")
    
    # 凭证类型（条件包含）
    if not filters.get("remove_voucher_type_column"):
        group_by_fields.append("voucher_type")
        select_fields.append("voucher_type")
    
    # 账户类型（条件包含）
    if not filters.get("remove_account_type_column"):
        group_by_fields.append("account_type")
        select_fields.append("account_type")
    
    # 公司（条件包含）
    if not filters.get("remove_company_column"):
        group_by_fields.append("company")
        select_fields.append("company")
    
    # 如果没有任何分组字段，默认按account分组
    if not group_by_fields:
        group_by_fields = ["account"]
        select_fields.append("account")
    
    # 构建SELECT和GROUP BY子句
    select_clause = ", ".join(select_fields)
    group_by_clause = ", ".join(group_by_fields)
    
    # 构建WHERE子句（公司筛选）
    where_conditions = []
    params = []  # 初始化参数列表
    
    # 添加日期范围条件
    where_conditions.append("posting_date >= %s")
    where_conditions.append("posting_date <= %s")
    params.extend([year_start, report_date])
    
    # 处理公司筛选
    if filters.company and len(filters.company) > 0:
        placeholders = ", ".join(["%s"] * len(filters.company))
        where_conditions.append(f"company IN ({placeholders})")
        params.extend(filters.company)
    
    # 组合WHERE子句
    where_clause = " AND ".join(where_conditions) if where_conditions else ""
    if where_clause:
        where_clause = f"WHERE {where_clause}"
    
    # 完整SQL查询
    sql = f"""
    SELECT 
        {select_clause},
        SUM(CASE WHEN posting_date = %s THEN amount ELSE 0 END) AS today,
        SUM(CASE WHEN posting_date BETWEEN %s AND %s THEN amount ELSE 0 END) AS this_week,
        SUM(CASE WHEN posting_date BETWEEN %s AND %s THEN amount ELSE 0 END) AS this_month,
        SUM(CASE WHEN posting_date BETWEEN %s AND %s THEN amount ELSE 0 END) AS this_quarter,
        SUM(CASE WHEN posting_date BETWEEN %s AND %s THEN amount ELSE 0 END) AS this_year
    FROM `tabPayment Ledger Entry`
    {where_clause}
    GROUP BY 
        {group_by_clause}
    ORDER BY 
        {group_by_clause}
    """
    
    # 添加CASE语句中的日期参数
    case_params = [
        report_date,                  # today
        week_start, report_date,      # this_week
        month_start, report_date,     # this_month
        quarter_start, report_date,   # this_quarter
        year_start, report_date       # this_year
    ]
    params = case_params + params  # 合并参数
    
    # 执行查询
    data = frappe.db.sql(sql, params, as_dict=True)
    
    # 获取公司货币信息（确保缓存兼容Redis）
    company_currencies = get_company_currencies()
    
    # 处理映射和货币信息
    for row in data:
        company = row.get("company")
        row["currency"] = company_currencies.get(company) if company else "USD"
        
        # 确定report_item
        account = row.get("account")
        if company and account:
            row["report_item"] = account_mappings.get((company, account), get_default_report_item(row.get("account_type")))
        elif account:
            matched = [v for (c, a), v in account_mappings.items() if a == account]
            row["report_item"] = matched[0] if matched else get_default_report_item(row.get("account_type"))
        else:
            row["report_item"] = "Aggregated"
    
    return data


def get_default_report_item(account_type):
    """根据账户类型返回默认报表项名称"""
    if account_type and "Receivable" in account_type:
        return "Receivable-undefined"
    elif account_type and "Payable" in account_type:
        return "Payable-undefined"
    else:
        return "General-undefined"


def get_company_currencies():
    """获取所有公司的默认货币（使用JSON序列化确保Redis兼容性）"""
    cache_key = "company_currencies"
    # 从缓存获取并反序列化
    cached_data = frappe.cache().get(cache_key)
    
    if cached_data:
        try:
            # 尝试反序列化
            return json.loads(cached_data)
        except (json.JSONDecodeError, TypeError):
            # 缓存数据损坏时重新生成
            pass
    
    # 从数据库获取
    companies = frappe.get_all("Company", fields=["name", "default_currency"])
    currencies = {
        company["name"]: company["default_currency"] 
        for company in companies 
        if isinstance(company["name"], str) and isinstance(company["default_currency"], str)
    }
    
    # 序列化后存入缓存（确保只包含字符串）
    frappe.cache().setex(cache_key, 3600, json.dumps(currencies))
    
    return currencies or {}


def get_report_summary(data):
    total_today = sum(row["today"] for row in data)
    total_week = sum(row["this_week"] for row in data)
    total_month = sum(row["this_month"] for row in data)
    
    return [
        {"label": _("Total Today"), "value": frappe.format_value(total_today, {"fieldtype": "Currency"}), "indicator": "blue"},
        {"label": _("Total This Week"), "value": frappe.format_value(total_week, {"fieldtype": "Currency"}), "indicator": "green"},
        {"label": _("Total This Month"), "value": frappe.format_value(total_month, {"fieldtype": "Currency"}), "indicator": "orange"}
    ]
    